package com.huizhan.web.common.utils;

import java.text.*;
import java.util.Date;
import java.io.*;
import java.util.*;
import java.lang.String;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import java.lang.reflect.*;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

 public class POIUtil {

 /**
     * 日期格式化
     */
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm");

    /**
     * 整数格式化,取所有整数部分
     */
    private static final DecimalFormat df = new DecimalFormat("#");


    /**
     * 读取Excel表格表头的内容
     * @param inputStream
     * @return String 表头内容的数组
     */
    public static List<String> readExcelTitle(InputStream inputStream, String fileName) throws Exception {
        List<String> titles = Lists.newArrayList();
        Workbook workbook = getWorkbook(inputStream, fileName);
        if (workbook == null) {
            return titles;
        }
        Sheet sheet = workbook.getSheetAt(0);
        //excel为空
        if (sheet.getLastRowNum() == 0 && sheet.getPhysicalNumberOfRows() == 0) {
            return titles;
        }
        //得到首行的row
        Row row = sheet.getRow(0);
        //标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        for (int i = 0; i < colNum; i++) {
            titles.add(getStringCellValue(row.getCell(i)));
        }
        return titles;
    }

    /**
     * 读取excel文件内容
     * @param inputStream 文件输入流, excel具体内容
     * @param fileName 文件名,通过文件后缀名判断excel版本
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcelContent(InputStream inputStream, String fileName) throws Exception {
        List<List<String>> result = Lists.newArrayList();
        Workbook workbook = getWorkbook(inputStream, fileName);
        if (workbook == null) {
            return result;
        }
        try {
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            if (row == null) {
                return result;
            }
            int colNum = row.getLastCellNum();
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 0; i <= rowNum; i++) {
                row = sheet.getRow(i);
                // 过滤空白行
                if (isBlankRow(colNum, row)) {
                    continue;
                }
                List<String> rowContents = Lists.newArrayList();
                for (int j = 0; j < colNum; j++) {
                    rowContents.add(getCellValue(row.getCell(j)));
                }
                result.add(rowContents);
            }
        } catch (Exception e) {
            throw new Exception("excel解析失败！");
        }

        return result;
    }

    private static boolean isBlankRow(int colNum, Row row) {
        for (int i = 0; i <= colNum; i++) {
            // 只要有一列不为空, 该行就不为空行
            if (Strings.isNullOrEmpty(getCellValue(row.getCell(i)))) {
                return false;
            }
        }
        return true;
    }

    
    /**
     * 获取workbook
     */
    public static Workbook createWorkbook(String fileName) throws Exception {
        Workbook workbook = null;
        if (isExcel2003(fileName)) {
            //2003 版本的excel
            workbook = new HSSFWorkbook();
        } else if (isExcel2007(fileName)) {
            //2007 版本的excel
            workbook = new XSSFWorkbook();
        } else {
            throw new Exception("file is not excel!");
        }
        return workbook;
    }


    /**
     * 获取workbook
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileName) throws Exception {
        Workbook workbook = null;
        if (isExcel2003(fileName)) {
            //2003 版本的excel
            workbook = new HSSFWorkbook(inputStream);
        } else if (isExcel2007(fileName)) {
            //2007 版本的excel
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new Exception("Excel文件格式有误！");
        }
        return workbook;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private static String getStringCellValue(Cell cell) {
        String cellValue = StringUtils.EMPTY;
        if (cell != null) {
            cellValue = cell.getStringCellValue().trim();
        }
        return cellValue;
    }

    /**
     * 对表格中数值进行格式化
     * @param cell Excel单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        String value = StringUtils.EMPTY;
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    value = cell.getRichStringCellValue().getString().trim();
                    break;
                case NUMERIC:
                    if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                        value = sdf.format(cell.getDateCellValue());
                    } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        value = sdf.format(date);
                    } else {
                        value = df.format(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case BLANK:
                    break;
                default:
                    break;
            }
        }
        return value;
    }

    /**
     * 是否是2003的excel，返回true是2003
     */
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 是否是2007的excel，返回true是2007
     */
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
    
    public static File writeExcel(String filePath, List<String> titleList, List<List<String>> dataList, Class<?> clazz) throws Exception {
        if(CollectionUtils.isEmpty(dataList)){
            return new File(filePath);
        }
        Workbook workbook = null;
        File file = new File(filePath);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file);

            int colNum = dataList.get(0).size();
            Map<Integer, Class<?>> colTypeMap = buildExcelColTypeMap(clazz);
            if (MapUtils.isEmpty(colTypeMap) || colTypeMap.size() != colNum) {
                throw new Exception("clazz type not math the excel cols");
            }
            fos = new FileOutputStream(file);
            workbook = createWorkbook(filePath);
            Sheet sheet = workbook.createSheet("sheet1");

            // excel表头
            Row row = sheet.createRow(0);
            for (int index = 0; index < colNum; index++) {
                Cell cell = row.createCell(index);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(titleList.get(index));
            }

            // 数据内容从第一行开始
            for (int i = 0; i < dataList.size(); i++) {
                row = sheet.createRow(i + 1);
                List<String> rowContent = dataList.get(i);
                for (int j = 0; j < colNum; j++) {
                    Class fieldType = colTypeMap.get(j + 1);
                    Cell cell = row.createCell(j);
                    writeCellValue(workbook, cell, rowContent.get(j), fieldType);
                }
            }
            workbook.write(fos);
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return file;
    }

    private static void writeCellValue(Workbook workbook, Cell cell, String value, Class fieldType) throws Exception {
        try {
            if (Integer.class == fieldType || java.lang.Long.class == fieldType) {
                double d = NumberUtils.toDouble(String.valueOf(value));
                cell.setCellValue(d);
                cell.setCellType(CellType.NUMERIC);
            } else if (java.lang.Double.class == fieldType || java.lang.Float.class == fieldType) {
                double d = NumberUtils.toDouble(String.valueOf(value));
                cell.setCellValue(d);
                cell.setCellType(CellType.NUMERIC);
            } else if (java.util.Date.class == fieldType) {
                CellStyle cellStyle = workbook.createCellStyle();
                short format = workbook.createDataFormat().getFormat("yyyy-mm-dd HH:mm");
                cellStyle.setDataFormat(format);
                Date date = DateUtils.parseDate(value, "yyyy-mm-dd HH:mm");
                cell.setCellValue(date);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.NUMERIC);
            } else if (java.lang.String.class == fieldType) {
                cell.setCellValue(value);
                cell.setCellType(CellType.STRING);
            } else if (java.lang.Boolean.class == fieldType) {
                cell.setCellValue(Boolean.valueOf(value));
                cell.setCellType(CellType.BOOLEAN);
            } else {
                cell.setCellValue("");
                cell.setCellType(CellType.BLANK);
            }
        } catch (Exception e) {
            throw new Exception("invalid cell value format");
        }

    }

    /**
     * 构建excel每一列的类型
     * @param clazz 标有ExcelProperty的注解
     * @return
     */
    private static Map<Integer, Class<?>> buildExcelColTypeMap(Class<?> clazz) throws Exception {
        Map<Integer, Class<?>> excelColTypeMap = Maps.newHashMap();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            if (!field.isAnnotationPresent(ExcelProperty.class)) {
                throw new Exception(String.format("Class field has no ExcelProperty Annotation", field));
            }
            ExcelProperty excelProperties = fields[i].getAnnotation(ExcelProperty.class);
            excelColTypeMap.put(excelProperties.rowIndex(), excelProperties.fieldType());
        }
        return excelColTypeMap;
    }

}
